﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using Microsoft.Office;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Runtime.InteropServices;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Drawing;
using System.Net.Mail;
using System.Net.Mime;
using System.IO;
using SQLExecute;
using System.Text;

/// <summary>
/// Summary description for Export
/// </summary>
public class Utility
{
    ReportDocument rptDoc = new ReportDocument();

    Profiles p = new Profiles();
    DataSet ds = new DataSet();
	public Utility()
	{		
	}
    protected void LoadImage(DataRow r, string strImageField, string strPath)
    {
        try
        {
            FileStream f = new FileStream(strPath, FileMode.Open, FileAccess.Read);
            byte[] img = new byte[f.Length];
            f.Read(img, 0, Convert.ToInt32(f.Length));
            f.Close();
            r[strImageField] = img;
        }

        catch (Exception ex)
        {
            //Response.Write("<font color=red>" + ex.Message + "</font>");
        }

    }
    public void Export2PDF(Guid id)
    {
        string strImageProfile = string.Empty;
        p.ID = id;
        ds = p.Get_Profile();
        ds.Tables[0].Columns.Add("image_profile",Type.GetType("System.Byte[]"));
        if (ds.Tables[0].Rows[0]["image_URL"].ToString() != "")
        {
            strImageProfile =HttpContext.Current.Server.MapPath(ds.Tables[0].Rows[0]["image_URL"].ToString());
            if (File.Exists(strImageProfile))
                LoadImage(ds.Tables[0].Rows[0], "image_stream", strImageProfile);
        }
        ds.WriteXmlSchema(HttpContext.Current.Server.MapPath("~/getProfile.xsd"));
        rptDoc = new ReportDocument();
        rptDoc.Load(HttpContext.Current.Server.MapPath("~/reports/rpt_profile8.rpt"));
        rptDoc.SetDataSource(ds.Tables[0]);
        rptDoc.ExportToDisk(ExportFormatType.PortableDocFormat, HttpContext.Current.Server.MapPath("~/export/profile.pdf"));
       
    }
    public string Export2Excel(Guid id)
    {
        string filename =HttpContext.Current.Server.MapPath("~/export/profile.xls");
        string fullname=string.Empty;
        string strAvailableDate, strBirth;
        DateTime dt;
        p.ID = id;

        ds = p.Get_Profile();

        Microsoft.Office.Interop.Excel._Application xl;
        Microsoft.Office.Interop.Excel._Workbook wBook;
        Microsoft.Office.Interop.Excel._Worksheet wSheet;
        Microsoft.Office.Interop.Excel.Range range;

       
        try
        {
            
            GC.Collect();
            xl = new Microsoft.Office.Interop.Excel.Application();
            
            wBook = xl.Workbooks.Open(filename, 0, false, 5, "", ""
                                , true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t"
                                , false, false, 0, true, 1, 0);
            wSheet = (_Worksheet)wBook.Sheets[1];


            wSheet.Cells[6, 3] = ds.Tables[0].Rows[0]["fullname"].ToString();
            fullname = ds.Tables[0].Rows[0]["fullname"].ToString();
            wSheet.Cells[6, 7] = ds.Tables[0].Rows[0]["sex"].ToString();
            wSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["pos"].ToString();
            if (ds.Tables[0].Rows[0]["birth"].ToString() != "")
            {
                dt = DateTime.Parse(ds.Tables[0].Rows[0]["birth"].ToString());
                strBirth = Common.Convert_MMDDYYYY_To_DDMMYYY(dt.ToShortDateString());
                //wSheet.Cells[7, 7] = DateTime.Parse(strBirth);
                wSheet.Cells[7, 7] = dt.ToShortDateString();
            }
            wSheet.Cells[8, 3] = ds.Tables[0].Rows[0]["id_card"].ToString();
            wSheet.Cells[8, 7] = ds.Tables[0].Rows[0]["known_from"].ToString();

            wSheet.Cells[9, 3] = ds.Tables[0].Rows[0]["current_address"].ToString();
            if (ds.Tables[0].Rows[0]["height"].ToString()!="0")
                wSheet.Cells[9, 7] = ds.Tables[0].Rows[0]["height"].ToString()+" (cm)";

            wSheet.Cells[10, 3] = ds.Tables[0].Rows[0]["phone"].ToString();
            if (ds.Tables[0].Rows[0]["weight"].ToString() != "0")
                wSheet.Cells[9, 7] = ds.Tables[0].Rows[0]["weight"].ToString() +" (kg)";

            wSheet.Cells[14, 3] = ds.Tables[0].Rows[0]["degree_type"].ToString();

            string strEducationBackground1 = string.Empty;
            string strWorkingExperience1=string.Empty;
            string strEducationBackground2 = string.Empty;
            string strWorkingExperience2=string.Empty;
            
            //Educational Background 1
            if (ds.Tables[0].Rows[0]["info_degree_date1"].ToString() != "")
                 strEducationBackground1 = ds.Tables[0].Rows[0]["info_degree_date1"].ToString();
            if (ds.Tables[0].Rows[0]["degree1"].ToString() != "")
            {
                if (ds.Tables[0].Rows[0]["info_degree_date1"].ToString() != "")
                    strEducationBackground1 += ": " + ds.Tables[0].Rows[0]["degree1"].ToString();
                else strEducationBackground1 +=  ds.Tables[0].Rows[0]["degree1"].ToString();
            }
            if (ds.Tables[0].Rows[0]["schoolname1"].ToString() != "")
                strEducationBackground1 += ", " + ds.Tables[0].Rows[0]["schoolname1"].ToString();
            if (ds.Tables[0].Rows[0]["trainingname1"].ToString() != "")
                strEducationBackground1 += " ," + ds.Tables[0].Rows[0]["trainingname1"].ToString();
            if (ds.Tables[0].Rows[0]["major_name1"].ToString() != "")
                strEducationBackground1 += ", ngành " + ds.Tables[0].Rows[0]["major_name1"].ToString();

            //Educational Background 2

            if (ds.Tables[0].Rows[0]["info_degree_date2"].ToString() != "")
                strEducationBackground2 = ds.Tables[0].Rows[0]["info_degree_date2"].ToString();
            if (ds.Tables[0].Rows[0]["info_degree2"].ToString() != "")
                strEducationBackground2 += ": " + ds.Tables[0].Rows[0]["degree2"].ToString();
            if (ds.Tables[0].Rows[0]["schoolname2"].ToString() != "")
                strEducationBackground2 += ", " + ds.Tables[0].Rows[0]["schoolname2"].ToString();
            if (ds.Tables[0].Rows[0]["trainingname2"].ToString() != "")
                strEducationBackground2 += " ," + ds.Tables[0].Rows[0]["trainingname2"].ToString();
            if (ds.Tables[0].Rows[0]["major_name2"].ToString() != "")
                strEducationBackground2 += ", ngành " + ds.Tables[0].Rows[0]["major_name2"].ToString();

            if (ds.Tables[0].Rows[0]["pos1"].ToString() != "")
                strWorkingExperience1 += ds.Tables[0].Rows[0]["pos1"].ToString();
            if (ds.Tables[0].Rows[0]["employer1"].ToString() != "")
                strWorkingExperience1 += ", " + ds.Tables[0].Rows[0]["employer1"].ToString();


            if (ds.Tables[0].Rows[0]["pos2"].ToString() != "")
                strWorkingExperience2 += ds.Tables[0].Rows[0]["pos2"].ToString();
            if (ds.Tables[0].Rows[0]["employer2"].ToString() != "")
                strWorkingExperience2 += ", " + ds.Tables[0].Rows[0]["employer2"].ToString();

            

            wSheet.Cells[15, 3] = strEducationBackground1;
            wSheet.Cells[16, 3] = strEducationBackground2;

            wSheet.Cells[17, 3] = strWorkingExperience1;
            wSheet.Cells[18, 3] = strWorkingExperience2;


            //EVALUATION RESULTS
            wSheet.Cells[21, 2] = ds.Tables[0].Rows[0]["interview1"].ToString();
            wSheet.Cells[22, 2] = ds.Tables[0].Rows[0]["interviewpos1"].ToString();
            wSheet.Cells[23, 2] = ds.Tables[0].Rows[0]["comment1"].ToString();
            wSheet.Cells[24, 2] = ds.Tables[0].Rows[0]["interview_conclusion"].ToString();//decision
            wSheet.Cells[26, 3] = ds.Tables[0].Rows[0]["english_result"].ToString();
            wSheet.Cells[26, 5] = ds.Tables[0].Rows[0]["word_result"].ToString();
            wSheet.Cells[26, 7] = ds.Tables[0].Rows[0]["excel_result"].ToString();

            wSheet.Cells[28, 2] = ds.Tables[0].Rows[0]["interview2"].ToString();
            wSheet.Cells[29, 2] = ds.Tables[0].Rows[0]["interviewpos2"].ToString();
            wSheet.Cells[30, 2] = ds.Tables[0].Rows[0]["comment2"].ToString();
            wSheet.Cells[31, 2] = ds.Tables[0].Rows[0]["interview_conclusion1"].ToString();//decision

            //wSheet.Cells[33, 2] = ds.Tables[0].Rows[0]["comment3"].ToString();
            wSheet.Cells[34, 2] = ds.Tables[0].Rows[0]["interviewpos3"].ToString();
            wSheet.Cells[35, 2] = ds.Tables[0].Rows[0]["comment3"].ToString();
            wSheet.Cells[36, 2] = ds.Tables[0].Rows[0]["final_decision"].ToString();
            

            //RECRUITMENTINFORMATION

            wSheet.Cells[41, 2] = ds.Tables[0].Rows[0]["recruitment_conclusion"].ToString();
            wSheet.Cells[42, 2] = ds.Tables[0].Rows[0]["company_id"].ToString();
            wSheet.Cells[41, 4] = ds.Tables[0].Rows[0]["position_offer"].ToString();
            wSheet.Cells[42, 4] = ds.Tables[0].Rows[0]["dept"].ToString();
            if (ds.Tables[0].Rows[0]["starting_date"].ToString() != "")
            {
                dt = DateTime.Parse(ds.Tables[0].Rows[0]["starting_date"].ToString());
                wSheet.Cells[43, 4] = dt.ToShortDateString();


            }

            if (ds.Tables[0].Rows[0]["salary"].ToString()!="0")
                wSheet.Cells[44, 7] = ds.Tables[0].Rows[0]["salary"].ToString();
            wSheet.Cells[45, 4] = ds.Tables[0].Rows[0]["note"].ToString();
            
            //insert image 
            if (ds.Tables[0].Rows[0]["image_URL"].ToString() != "")
            {
                filename = HttpContext.Current.Server.MapPath(ds.Tables[0].Rows[0]["image_URL"].ToString());


                range = wSheet.get_Range("A6", "A13");
                float l = (float)((double)range.Left);
                float t = (float)((double)range.Top);

                wSheet.Shapes.AddPicture(filename
                                        , Microsoft.Office.Core.MsoTriState.msoFalse
                                        , Microsoft.Office.Core.MsoTriState.msoCTrue
                                        , l, t, 120, 140);
            }
            else
            {
                filename = HttpContext.Current.Server.MapPath("~/images/photos.jpg");


                range = wSheet.get_Range("A6", "A13");
                float l = (float)((double)range.Left);
                float t = (float)((double)range.Top);

                wSheet.Shapes.AddPicture(filename
                                        , Microsoft.Office.Core.MsoTriState.msoFalse
                                        , Microsoft.Office.Core.MsoTriState.msoCTrue
                                        , l, t, 120, 140);
            }
            filename = HttpContext.Current.Server.MapPath("~/export/") +"profile_" + ds.Tables[0].Rows[0]["fullname"].ToString();
            wBook.SaveAs(filename 
            ,  Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
            , Type.Missing
            , Type.Missing
            , Type.Missing, Type.Missing,  Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange
            , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            

            wBook.Close(null, null, null);
            xl.Workbooks.Close();
            xl.Quit();

            //Marshal.ReleaseComObject(range);
            Marshal.ReleaseComObject(xl);
            Marshal.ReleaseComObject(wSheet);
            Marshal.ReleaseComObject(wBook);

       

        }
        catch (Exception ex)
        {
            //System.Web.HttpResponse
            //Response.Write(theException.Message);
           

        }
        return fullname;

    }
    public void SendMail(string fullname,string mail_from, string mailto,string subject, string content)
    {

        System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*");
        bool result = regex.IsMatch(mailto);
        string strMessageBody;
        string strLogo;
        AlternateView alternateView;
        SmtpClient s;
        MailAddress mail_cc = new MailAddress("oanhnguyen@educapital.edu.vn");
        if (result)
        {
            try
            {
                StreamReader streamReader = new StreamReader(HttpContext.Current.Server.MapPath("~/mail/template.htm"));
                streamReader = File.OpenText(HttpContext.Current.Server.MapPath("~/mail/template.htm"));
                string strContentHtml = streamReader.ReadToEnd();
                streamReader.Close();

                strMessageBody = strContentHtml.Replace("<Ten_Ung_Vien>", fullname);
                strMessageBody = strMessageBody.Replace("<image logo here>", "<img src=cid:imgLogo />");
                strMessageBody = strMessageBody.Replace("<Noi_dung_gui_mail>", content);

                strLogo = HttpContext.Current.Server.MapPath("~/images/logo_edu.jpg");
                LinkedResource logo = new LinkedResource(strLogo);
                logo.ContentId = "imgLogo";
                ContentType mimeType = new System.Net.Mime.ContentType("text/html");
                alternateView = AlternateView.CreateAlternateViewFromString(strMessageBody, mimeType);

                alternateView.LinkedResources.Add(logo);
                //run send mail line here
                //System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage(mail_from, mailto);

                System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage(mail_from, "quangkid@yahoo.com");
                //System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage("recruitment@vas.edu.vn", "quangtran@vus-etsc.edu.vn");
                mailMessage.AlternateViews.Add(alternateView);
                mailMessage.Body = strMessageBody;

                mailMessage.IsBodyHtml = true;
                mailMessage.Subject = subject;

                mailMessage.CC.Add(mail_cc);

                if (mail_from=="recruitment@vus-etsc.edu.vn")
                      s = new SmtpClient("192.168.0.100");
                else
                      s = new SmtpClient("mail.vas.edu.vn");
                s.Send(mailMessage);

            }
            catch (Exception ex)
            {
               // Response.Write(ex.Message);

                return;
            }

        }

    }
    public void Insert_Mail_Send_Info(Guid id                                   
                                    , string mail_to
                                    ,DateTime date_sended
                                    ,string content
                                    ,string mail_from
                                    )
    {
        SqlHelper.ExecuteNonQuery(Common.strConnect, "Insert_Mail_Send_Info"
                                  , new object[] {id,mail_to,date_sended,content,mail_from });

    }
    public string Export2PDF2(Guid id)
    {

        string strImageProfile;
        string strFullName;
        profile ds = new profile();

        SqlConnection cn = new SqlConnection(Common.strConnect);
        SqlCommand sqlCommand = new SqlCommand("Get_Profile", cn);
        sqlCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter param = sqlCommand.Parameters.Add("profile_id", SqlDbType.UniqueIdentifier);
        param.Value = id;


        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        sqlDataAdapter.Fill(ds.Tables[0]);
        strFullName = ds.Tables[0].Rows[0]["fullname"].ToString();
        strFullName = Remove_VietnameseSigns(strFullName);
      
        if (ds.Tables[0].Rows[0]["image_URL"].ToString() != "")
        {
            strImageProfile = HttpContext.Current.Server.MapPath(ds.Tables[0].Rows[0]["image_URL"].ToString());
            if (File.Exists(strImageProfile))
                LoadImage(ds.Tables[0].Rows[0], "image_stream", strImageProfile);
        }
         rptDoc = new ReportDocument();
         rptDoc.Load(HttpContext.Current.Server.MapPath("~/reports/rpt_profile9.rpt"));
         rptDoc.SetDataSource(ds.Tables[0]);
         rptDoc.ExportToDisk(ExportFormatType.PortableDocFormat, HttpContext.Current.Server.MapPath("~/export/" + strFullName+".pdf"));
        //ClientScript.RegisterStartupScript(this.Page.GetType(), "popupOpener", "var popup=window.open('../export/profile.pdf');popup.focus();", true);

         return strFullName+".pdf";

    }

    public string Remove_VietnameseSigns(string str)
    {

      string[] VietnameseSigns = new string[]
             {
              "aAeEoOuUiIdDyY",
              "áàạảãâấầậẩẫăắằặẳẵ",
              "ÁÀẠẢÃÂẤẦẬẨẪĂẮẰẶẲẴ",
              "éèẹẻẽêếềệểễ",
              "ÉÈẸẺẼÊẾỀỆỂỄ",
              "óòọỏõôốồộổỗơớờợởỡ",
              "ÓÒỌỎÕÔỐỒỘỔỖƠỚỜỢỞỠ",
              "úùụủũưứừựửữ",
              "ÚÙỤỦŨƯỨỪỰỬỮ",
              "íìịỉĩ",
              "ÍÌỊỈĨ",
              "đ",
              "Đ",
              "ýỳỵỷỹ",
              "ÝỲỴỶỸ"
             };

      for (int i = 1; i < VietnameseSigns.Length; i++)      
          for (int j = 0; j < VietnameseSigns[i].Length; j++)
          {
              str = str.Replace(VietnameseSigns[i][j], VietnameseSigns[0][i - 1]);

          }
      
        return str;
    }
}
